package dao;

import model.Rank;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.C3P0Util;

import java.sql.SQLException;
import java.util.List;

/**
 *
 */
public class RankDAO {
    /**
     * 获得排行list
     *
     * @return
     */
    public List<Rank> getRank() {
        List<Rank> list = null;
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        String sql = "select * from rank";
        Object[] params = {};
        try {
            list = (List<Rank>) qr.query(sql, new BeanListHandler(Rank.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 更新排行榜
     */
    public void updateRank() {
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        String sql1 = "delete from rank;";
        String sql2 = "insert into rank(comic_id,comic_name,count) select id,name,count(comic_id) as count from comic A left join collection B on A.id =B.comic_id group by id order by count desc limit 10";
        try {
            qr.update(sql1);
            qr.update(sql2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
